
var mysql  = require('mysql');
var url = require("url")
var fs = require("fs")
var http = require('http')
var request = require('request')
var path = require('path')
var child_process = require('child_process')
var images = require("images")
var multiparty = require('multiparty')
var qs = require('querystring');
require('./functions.js')

var sep = path.sep

var showNumsPerPage_G = 50
var names_G = ['adv_camp_id','offer_name','platform','payin','geo_country','appid','preview_link','icon_link','creative_link']
var tdWidth_G = [50,50,50,15,50,50,50,50,50]

var host = 'http://res.ad688.com/'
//var host = '192.168.1.4/'

var adResPath = 'images'+ sep + 'adRes' + sep

this.loadResProgress = [0,0] // 0总共  1当前进度
this.offers_G = []
this.icon_linkUpdate_reqs = []


function getSqlConnection()
{
	var connection = mysql.createConnection({host: 'rdsea0aj9n7m3g48ta5u.mysql.rds.aliyuncs.com', database: 'ad688com', user:'ad688com',password : 'ad688com',port: '3306',dateStrings: true ,}); 
	//var connection = mysql.createConnection({host: 'game1715.com', database: 'test', user:'t',password : 's123',port: '3306',dateStrings: true ,}); 
	return connection
} 

this.dealOfferList = function(req, res)
{   
    var env = this
    var queryUrl = url.parse(req.url).query 
    var query = qs.parse(queryUrl)
    var advertiser_id = query.advertiser_id
    var pageIndex = query.pageIndex
    var isAll = query.isAll
    var targetOffers = []

    if(isAll)
    {   
        targetOffers = env.offers_G[advertiser_id] || []
    }
    else
    {
        var body = req.body
        advertiser_id = body.advertiser_id
        pageIndex = body.pageIndex
        var offers = env.offers_G[advertiser_id]
        for(var i in offers)
        {   
            var offer = offers[i]
            for(var j in req.body)
            {
              if(j=='advertiser_id')
                continue
              if(offer.adv_camp_id == body[j])
              {
                targetOffers[targetOffers.length] = offer
              }
            }
        }
    }
    // var pageIndex = query.pageIndex || 1

    if(query.icon_linkUpdate)
    { 
        displayOfferList(advertiser_id, pageIndex, res, env)

        //终止之前的网络请求
        var reqs = env.icon_linkUpdate_reqs[advertiser_id] || []
        for(var i=reqs.length;i>=0;i--)
        { 
          var req = reqs[i]
          if(req && req.response)
          {
            req.response.destroy()
          }
          reqs.splice(i,1)
        }

        for(var j in targetOffers)
        {  
          function fun1(offer)
          {
            function onAsynResult(preview_link, icon_link)
            {
              offer.preview_link = preview_link || ''
              offer.icon_link = icon_link || ''
            }
            env.icon_linkUpdate(advertiser_id, offer.preview_link,offer.redirect_format, offer.platform, onAsynResult)
          }
          if(!targetOffers[j].icon_link)
            fun1(targetOffers[j])
        }
          
    }
    else if(query.loadCreative)
    {   
        env.loadCreatives(advertiser_id, targetOffers)

        displayOfferList(advertiser_id, pageIndex, res, env)
    }
    else if(query.creative_LinkUpdate)
    {
        for(var i in targetOffers)
        {  
          function fun2(offer)
          {
            function onCreative_LinkUpdate(creative_link)
            {
              offer.creative_link = creative_link
            }

            env.creative_LinkUpdate(advertiser_id, offer.adv_camp_id, onCreative_LinkUpdate)
          }
          fun2(targetOffers[i])
        }
        
        displayOfferList(advertiser_id, pageIndex, res, env)
    }
    else if(query.filterAppid)
    { 
        var offers = []
        var originOffers = clone(targetOffers)
        for(var i in originOffers)
        {
          if(originOffers[i].appid)
            offers[offers.length] = originOffers[i]
        }
        var body = getHtmlByJson(offers, advertiser_id, pageIndex, null, env)
        res.write(body)
        res.end()
    }
    else
    {   
        displayOfferList(advertiser_id, pageIndex, res, env)
    }

}

function displayOfferList(advertiser_id, pageIndex, response, env)
{
  var onGetOfferList = function(data, env)
  { 
    var body = getHtmlByJson(data, advertiser_id, pageIndex, null, env)
    response.write(body)
    response.end()
  }
  getOfferList(advertiser_id, env, onGetOfferList)

}

function getApi(advertiser_id, onGet)
{
  // var postData = fs.readFileSync('public/appflood.json').toString()
  // onGet('[' + postData + ']')
  // return 
  var config = JSON.parse( fs.readFileSync('partnerConfigs/' + advertiser_id + '.json').toString() ) 
  var api_urls = config.api_urls.split('|')
  var postDatas = []
  var hasBackNum = 0
  for(var i in api_urls)
  {
    function onGetPostData(idx, postData)
    {
      postDatas[idx] = postData
      hasBackNum = hasBackNum + 1
      if(hasBackNum == api_urls.length )
        onGetAllPostdata(postDatas)
    }
    getPostData(i, onGetPostData)
  }

  function onGetAllPostdata(postDatas)
  {   
      console.log('postDatas::',postDatas.length)
      var result = '['
      for(var j=0;j<postDatas.length;j++)
      {
        //去掉postData的头尾的{}
        var postData = postDatas[j]
        //console.log('postData::',postData)
        result = result + postData 
        if(j < postDatas.length-1)
          result = result + ','
      }
      result = result + ']'
      onGet(result)
  }

  function getPostData(idx, onGetPostData)
  {
    var u = url.parse(api_urls[idx])
    var options = { 
        hostname:u.hostname, 
        port: 80, 
        path: u.path, 
        method: 'GET',  
    }
    console.log('getApi', idx)
    var postData = ""
    var req = http.request(options, 
    function(res) 
    {  
        res.setEncoding('utf8')
        res.on('data', function(data) {
            //console.log(data)
            postData += data
            })
        res.on('end', function() {    
                onGetPostData(idx, postData)
              console.log('getApi end',idx) 
            })
        res.on('error', function() {   
              console.log('getApi error',idx) 
              onGetPostData(idx, {})
            })
        res.on('close', function() {    
              onGetPostData(idx, {})
            })
    })
    req.end()
  }

}

function parseApiToObj(apiStr, advertiser_id) 
{
    var config = JSON.parse( fs.readFileSync('partnerConfigs/' + advertiser_id + '.json').toString() ) 
    try { 
      var roots = JSON.parse(apiStr)
    }
    catch (e) {
      return []
    }

    var offers = []
    //本地化offer
    for(var i in roots)
    {
      var body = eval( 'roots[i].' + config.api_bodyDir )
      for(var j in body)
      {
        var offer = getAllDataDataDefault()
        for(var jj in config)
        {
          if(config[jj] && typeof(offer[jj])!='undefined')
          {
            offer[jj] = eval('body[j].' + config[jj])
          }
        }
        if(config.adv_camp_id == 'campIdIsIdx') 
          offer.adv_camp_id = j
        
        offer.advertiser_id = advertiser_id

        offers[offers.length] = offer
      }
    }

    return offers
}


function getOfferList(advertiser_id, env, onGet)
{ 

  if(env.offers_G[advertiser_id])
  {
      function onPassExistedOffers(offers)
      {
          env.offers_G[advertiser_id] = offers
          onGet(clone(env.offers_G[advertiser_id]), env)
      }
      passExistedOffers(advertiser_id, env.offers_G[advertiser_id], onPassExistedOffers) 
      return 
  }

  console.log('getOfferList')
  
  function onPassExistedOffers(offers)
  {
      env.offers_G[advertiser_id] = offers
      for(var i in offers)
      {
          formatOffer(advertiser_id, offers[i]) 
      }
      onGet(clone(env.offers_G[advertiser_id]), env)
  }
  onGetApi = function(postData)
  {
      passExistedOffers(advertiser_id, parseApiToObj(postData, advertiser_id) , onPassExistedOffers) 
  }

  getApi(advertiser_id, onGetApi)
}

//////////////////////////////////////////////////////////////////////////

function getBreakSpace(num)
{
  var s = ''
  for(var i=0;i<num;i++)
  {
    s = s + '&nbsp;'
  }
  return s
}


function getHtmlByJson(data, advertiser_id, pageIndex, showNumsPerPage, env)
{
    showNumsPerPage = showNumsPerPage || showNumsPerPage_G
    var advertiser_id = advertiser_id
    var pageIndex = pageIndex || 1
    var nameLength = names_G.length
    var dataLength = data.length
    var pageNum = Math.ceil(dataLength / showNumsPerPage) 

    var html =
    '<head>'+
    '<meta http-equiv="Content-Type" '+
    'content="text/html; charset=UTF-8" />'+
    '<script src="/javascripts/forClient.js"></script>'+ 
    '<style type="text/css">'+ 
    'td{overflow:hidden;white-space:nowrap; }'+  //将超出内容设为隐藏
    //'td{word-wrap:break-word}'+  //自动换行
    '</style>'+ 
    '<link rel="stylesheet" href="/stylesheets/style.css" />'+
    '</head>'+
    '<body>'+
    '<p>updateIconLink依据preview_link,这个操作同时会根据clickUrl获得preview_link</p>'+
    '<p>loadCreative依据creative_link，updateCreativeLink后除非清空缓存否则不要再次loadCreative</p>'+
    '<p>updateCreativeLink依据loadCreative下载的资源</p>'+
    '<p>右边的选择框被选中时 操作会对所有offer生效, 否则只会对被勾选中的offer生效 操作包括：提交sql、过滤、icon_linkUpdate、loadCreative、creative_LinkUpdate</p>'+

    '<p><a href="/">首页</a></p>'+
    '<p><a href="javascript:void(0)" onclick="refreshOfferList()">刷新</a></p>'+
    '<p><a href="/partnerList">返回</a></p>'+
    '<table    align="center" width="1000" style="table-layout:fixed"><tr>' + 
    '<th width=50><lable id = "advertiser_id" >' + advertiser_id  + '</lable></th>' +
    '<th width=50><button onclick="chooseAllOnCurrentPage()">全勾</button></th>'+
    '<th width=60><button onclick="addToSql()">提交sql</button></th>'+

    '<th width=60><button onclick="filterAppid()">过滤</button></th>'+

    '<th width=800>'+
      '<table align="center">' + 
      '<tr>'+
      '<td><button onclick="previous()">上一页</button></td>'+
      '<td><lable id = "pageIndex">' + pageIndex + '</lable></td>'+
      '<td><button onclick="next()">下一页</button></td>'+  
      '<td>共</td>'+
      '<td><lable id = "pageNum">' + pageNum +  '</lable></td>'+ 
      '<td>页</td>' +
      '<td><input style="width:50px;" type="text" id="gotoPage" /></td>' + 
      '<td><button onclick="goto()">go</button></td>'+  
      '<td><button onclick="icon_linkUpdate()">updateIconLink</button></td>'+  
      '<td><button onclick="loadCreative()">loadCreative</button></td>'+  
      '<td><button onclick="creative_LinkUpdate()">updateCreativeLink</button></td>'+  
      '<td><input id=isAll type="checkbox"  /></td>' +
      '</tr>'+
      '</table>' + 
    '</th>'+
    '<th width=0 align = "right"><lable>' + env.loadResProgress[1] + '/' + env.loadResProgress[0] + '</lable></th>' +
    '</tr></table>' + 
    '<table width="1000" border="1" cellspacing="0" cellpadding="10" bordercolor="#7C7C7C"'+ 
    'align = "center" style="table-layout:fixed">'

    //第一排是变量名字
    html = html + '<tr>'
    for(i=0;i<nameLength;i++)
    {
      html = html +  '<th width=' + tdWidth_G[i] + '>' + names_G[i] + '</th>'
    }
    html = html + '</tr>'

    var s = (pageIndex -1) * showNumsPerPage
    var e = Math.min(dataLength, s + showNumsPerPage)
    for(i=s;i<e&&data[i];i++)
    {
      html = html + '<tr>'
      for(j=0;j<nameLength;j++)
      {
        var td = data[i][names_G[j]]
        if(j==0)
            td = td + getBreakSpace(5) + '<input id=' + i + ' type="checkbox" value=' + td + ' />'
        html = html +  '<td>' + td + '</td>'
      }
      html = html + '</tr>'
    }
    html = html + 
    '</table>' + 
    '<br><div align = "center" ><lable id = "showNumsPerPage" >' + showNumsPerPage + '</lable>/页</div>' +
    '</body>'
  
    return html
}

/////////////localize offer/////////////

//过滤已经录入的offer 
//onAsynResult 异步操作完成了才会回调 回传参数是最新的offers
function passExistedOffers(advertiser_id, offers, onAsynResult) 
{ 
  if(offers.length==0)
  {
    onAsynResult(offers)
    return
  }

  var connection = getSqlConnection()
  var sql = 'SELECT * FROM lib_offer WHERE advertiser_id = ' + advertiser_id

  connection.query(sql, function (err, rows) 
  {
      if(err)
      {
        console.log('passExistedOffers error - ',err.message);
        connection.end();
        return   
      }        
      connection.end()
      for(var i in rows)
      {
        for(var j in offers)
        {
          if( parseInt(offers[j].adv_camp_id)  == parseInt(rows[i].adv_camp_id) )
          {
            offers.splice(j,1)
            break
          }
        }
      }
      onAsynResult?onAsynResult(offers):''
  })

  return offers
}


//处理creativelink等 得到最后可以使用的offer 
function formatOffer(advertiser_id, offer) 
{
  //geo_country
  if(offer.geo_country instanceof Array)
    offer.geo_country = offer.geo_country.join(",")

  //cap_all_today
  offer.cap_all_today = 1000

  //platform
  var p = []
  if(offer.platform instanceof Array)
  {
    for(var i in offer.platform)
    {
      var s = offer.platform[i].toLowerCase()
      if(s=='android')
      {
        p[p.length] = 'Android'
      }
      else if(s=='ios')
      {
        p[p.length] = 'iOS'
      }
    }
  }
  else if(typeof(offer.platform) == "string")
  {
    var s = offer.platform.toLowerCase()
    if(s.indexOf("android") >= 0)
    {
      p[p.length] = 'Android'
    }
    if(s.indexOf("ios") >= 0)
    {
      p[p.length] = 'iOS'
    }
  }
  offer.platform = p.join("/")
  //devices
  if(offer.devices instanceof Array)
    offer.devices = offer.devices.join("/")


  //appid previewlink
  if(offer.platform == 'Android')
  { 
    if(offer.appid )
    {
      offer.preview_link = 'https://play.google.com/store/apps/details?id=' + offer.appid
    }
    else if(!offer.appid && offer.preview_link)
    { 
      offer.appid = offer.preview_link.replace(/.*id=([^\?]+).*/, "$1")
    }
  }
  else if(offer.platform == 'iOS')
  {
    if(offer.appid)
    { 
      offer.preview_link = 'https://itunes.apple.com/app/id' + offer.appid
      //ios 的前缀不固定 icon_linkUpdate中通过redirect_format 异步获取  有的offer需要用对应国家vpn才能跳到商店(极少数 不处理了)
      //offer.preview_link = 'https://itunes.apple.com/us/app/banggood-shopping-with-fun/id' + offer.appid
    }
    else if(!offer.appid && offer.preview_link)
    { 
      offer.appid = offer.preview_link.replace(/.*\/id([^\?]+).*/, "$1")
    }
  }
  if(typeof(offer.payin)=='string')
    offer.payin = offer.payin.replace(/[^0123456789\.]/g, '')

  //icon_link creative_link  不放在拉api的时候处理（会卡！）\
  for(var i in offer)
  {
    if(typeof(offer[i])=='object')
    offer[i] = JSON.stringify(offer[i])
  }

  return offer
}

this.icon_linkUpdate = function(advertiser_id, preview_link, redirect_format, platform, onAsynResult) 
{   
    var env = this

    var _getIconViaPreviewLink = function()
    { 
        var reqs = env.icon_linkUpdate_reqs[advertiser_id] || []
        reqs[reqs.length] = getIconViaPreviewLink(preview_link, platform, onAsynResult)
    }

    var _getPreviewLink = function()
    {
      var options = {
        url: redirect_format
      }
      var req = request(options, function(error, response, body){})
      req.on('response', function(response) {
          preview_link = response.request.uri.href
          _getIconViaPreviewLink()
        })
    }

    if(preview_link)
    { 
      if(preview_link.indexOf('itunes.apple')>=0 || preview_link.indexOf('play.google')>=0)
      {
         _getIconViaPreviewLink()
      }
      else
      {
        onAsynResult?onAsynResult(preview_link, ''):''
      }
    }
    else if(!preview_link)
    {   
        if(redirect_format)
          _getPreviewLink() 
        else
          onAsynResult?onAsynResult('', ''):''
    }
}

function getIconViaPreviewLink(preview_link, platform, onAsynResult)
{   
    var options = {
      url: preview_link,
    }
    var req = request(options, function(error, response, body){})
    req.on('response', function(response) {

            var postData = ""
            response.on('error', function(data) {
                  onAsynResult?onAsynResult(preview_link, ''):''
                  console.log('getIconViaPreviewLink error',preview_link)
                })
            response.on('close', function(data) {
                  onAsynResult?onAsynResult(preview_link, ''):''
                  console.log('getIconViaPreviewLink close',preview_link)
                })
            response.on('data', function(data) {
                postData += data
                })
            response.on('end', function() {    
                    if(platform == 'Android')
                    { 
                      // class="cover-image" src="//
                      var start = postData.indexOf('class="cover-image" src="//')
                      if( start >= 0 )
                      {
                          var p = postData.substring(start, start + 300)
                          var icon_link = p.replace(/[^]*class=\"cover-image\" src=\"\/\/([^\"]+)\"[^]*/, "$1")
                          onAsynResult?onAsynResult(preview_link, icon_link):''
                      }
                    }
                    else if(platform == 'iOS')
                    {
                      //icon350x350
                      var start = postData.indexOf('icon350x350') - 300
                      if( start >= 0 )
                      {
                          var p = postData.substring(start, start + 600)
                          var icon_link = p.replace(/[^]*src-swap-high-dpi=\"([^\"]+\/icon350x350[^\"]+)\"[^]*/, "$1")
                          onAsynResult?onAsynResult(preview_link, icon_link):''
                      }
                    }
                    else
                    {
                      onAsynResult?onAsynResult(preview_link, ''):''
                    }
                })
      })
      req.on('error', function(data) {
            onAsynResult?onAsynResult(preview_link, ''):''
            console.log('req getIconViaPreviewLink error',preview_link)
          })
      req.on('close', function(data) {
            onAsynResult?onAsynResult(preview_link, ''):''
            console.log('req getIconViaPreviewLink close',preview_link)
          })
    return req
}

/**
 * [loadCreatives 用这个函数统一起来下载 单个下载的话数量多了会有问题 ]
 * @param  {[type]} advertiser_id       [description]
 * @param  {[type]} datas [{adv_camp_id:xxx, creative_link:xxx},{...}]
 * @param  {[type]} onLoads             [description]
 * @param  {[type]} onLoadAll           [description]
 * @param  {[type]} isNeedLoadRegexp              [必须匹配这个正则才会下载]
 * @return {[type]}                     [description]
 */
this.loadCreatives = function(advertiser_id, datas, onLoadOneCreative, onLoadAllCreative, isNeedLoadRegexp, isNeedInsertRegexp)
{
  var env = this
  var urls = []
  var targetPaths = []
  var groupNum = 50
  var resPath = 'public' + sep + adResPath + 'creative' + sep + advertiser_id + sep
    
  var onLoadOne = function(idx)
  {
    env.loadResProgress[1] = env.loadResProgress[1] + 1
    onLoadOneCreative?onLoadOneCreative(idx):''
  }
  var onLoadAll = function()
  {
      child_process.exec(chmod777S(resPath), function()
      {
          onLoadAllCreative?onLoadAllCreative():''
      })
  }
  isNeedLoadRegexp = isNeedLoadRegexp || '!!(((([0123456789]+x[0123456789]&&!!\\.gif))||!!((\\.gif|\\.png|\\.jpg|\\.jpeg))))'
  isNeedInsertRegexp = isNeedInsertRegexp || '[0123456789]+x[0123456789]&&!!\\.gif'



  env.loadResProgress = [0, 0]
  for(var i in datas)
  {
    console.log(datas[i].adv_camp_id)
    if(fs.existsSync(resPath + datas[i].adv_camp_id + sep))
       child_process.execSync(rmdirS + resPath + datas[i].adv_camp_id + sep)

    fun(datas[i])
  }

  function fun(data)
  {
    var creative_link = data.creative_link?JSON.parse(data.creative_link):[]
    var adv_camp_id = data.adv_camp_id
    if(!fs.existsSync( resPath + adv_camp_id + sep + 'other' ))
      child_process.execSync(mkdirS + resPath + adv_camp_id + sep + 'other')  

    var urls_paths = getUrlsFromCreativeLink(advertiser_id, adv_camp_id, creative_link, isNeedLoadRegexp, isNeedInsertRegexp) 
    for(var j in urls_paths)
    {
        urls[urls.length] = urls_paths[j][0]
        targetPaths[targetPaths.length] = urls_paths[j][1]
        env.loadResProgress[0] = env.loadResProgress[0] + 1
    }

  }

  loadReses(urls, targetPaths, groupNum, onLoadOne, onLoadAll)

}
getUrlsFromCreativeLink = function(advertiser_id, adv_camp_id, creative_link, isNeedLoadRegexp, isNeedInsertRegexp) 
{   

    var creative_urlDir = JSON.parse( fs.readFileSync('partnerConfigs' + sep + advertiser_id + '.json').toString() ).creative_urlDir 
    var resPath = 'public' + sep + adResPath + 'creative' + sep + advertiser_id + sep

    var urls_paths = []
    var isExclude  
    for(var i in creative_link)
    {
        var subCreative_link = creative_link[i]
        if(creative_urlDir=='000') 
        {
        }
        else if(creative_urlDir=='001') //001 不需要下载 只需要记录本地记好了
        {
          for(var j in subCreative_link)
          {
              var cr_link = {}
              cr_link.url = subCreative_link[j]
              var size = i.split('x')
              cr_link.width= size[0]
              cr_link.height = size[1]
              fs.appendFile(resPath + adv_camp_id + sep + 'other' + sep + 'noLoadRes.json', JSON.stringify(cr_link) + ',')
          }
        }
        else
        {
            var creativeUrl = subCreative_link[creative_urlDir] || subCreative_link.url 
            var c = creativeUrl.split('/')
            var targetPath = resPath + adv_camp_id + sep + c[c.length-1]
            
            isNeedLoad = true   
            var sizeIfExclude
            var str = ''
            for(var j in subCreative_link)
            {
                str = str + JSON.stringify(subCreative_link[j])
            }

            if(!isNeedLoadRegexp || isMatch_regexp(isNeedLoadRegexp, str) )
            {
              urls_paths[urls_paths.length] = [creativeUrl, targetPath]
            }

            if( isNeedInsertRegexp && isMatch_regexp(isNeedInsertRegexp, str) )
            {
                sizeIfExclude =  str.replace(/[^]*[^0123456789]([0123456789]+x[0123456789]+)[^]*/,'$1')
                var cr_link = {}
                cr_link.url = creativeUrl
                sizeIfExclude = sizeIfExclude.split('x')
                cr_link.width= sizeIfExclude[0]
                cr_link.height = sizeIfExclude[1]
                fs.appendFile(resPath + adv_camp_id + sep + 'other' + sep + 'noLoadRes.json', JSON.stringify(cr_link) + ',')
            }
        }
    }
    return urls_paths

}



this.creative_LinkUpdate = function(advertiser_id, adv_camp_id, onAsynResult) 
{ 

    onGet = function(creative_link)
    {
        onAsynResult?onAsynResult(creative_link):''
    }
    getCreativeLinkViaId(advertiser_id, adv_camp_id, onGet)

}

//获取creative_link格式的字符串
function getCreativeLinkViaId(advertiser_id, adv_camp_id, onGet)
{   
    var localResPath = 'public' + sep + adResPath + 'creative' + sep + advertiser_id + sep + adv_camp_id + sep
    var adResPaths = adResPath.split(sep)
    var urlPrefix = host + adResPaths[0] + '/' + adResPaths[1] + '/' + 'creative/' + advertiser_id + '/' + adv_camp_id + '/'
    var creative_link = []
    var hasDealFile = 0
    var filesL 
    fs.readFile(localResPath + sep + 'other' + sep + 'noLoadRes.json', function(err, data)
    {
        if(err){}
        else
        {   
          try
          {   
              var s = data.toString()
              var creative_linkNoLoad = JSON.parse('[' + s.substr(0,s.length-1) + ']')
              for(var i in  creative_linkNoLoad)
              {
                creative_link[creative_link.length] = creative_linkNoLoad[i]
              }
          }
          catch(e)
          {
            console.log('read noload error', e)
          }
        }
        fs.readdir(localResPath, onReaddir)
    })

    // fs.exists(localResPath + 'other', function(isExist)
    // {
    //    if(!isExist)
    //    {  
    //       //当一个系统调用(比如fork)因为没有足够的资源(比如虚拟内存)而执行失败，返回EAGAIN提示其再调用一次(也许下次就能成功)
    //       function mkd(cb, times)
    //       {
    //         if(times>0)
    //         {
    //           child_process.exec(mkdirS + localResPath + 'other', function(error)
    //            {
    //               if(error)
    //                 mkd(cb, times-1)
    //               else
    //                 cb()
    //            })
    //         }
    //         else
    //         {
    //           console.log('mkdir other fail',localResPath)
    //         }
    //       }

    //       mkd(function(){fs.readdir(localResPath, onReaddir)}, 3)

    //    }
    //    else
    //    {  
    //       fs.readdir(localResPath, onReaddir)
    //    }
    // })  

    function onReaddir(err, files)
    {
      if(err)
      { 
        hasDealFile = hasDealFile + 1
        callbackEnable()
        console.log('onReaddir error', err)
        return 
      }


      filesL = files.length
      for(var i=0;i<filesL;i++)  
      {
        if(files[i]=='other')
        {
          hasDealFile = hasDealFile + 1
          callbackEnable()
          continue
        }
        var sp = files[i].split('.')
        if(sp[1] == 'gif') //转化成jpg 存到other
        {   
           var jpgCopyPath = localResPath + 'other' + sep + sp[0] +  '.jpg'
           var originGifName = sp[0]
           function f1(jpgCopyPath, originGifName)
           {
                fs.exists(jpgCopyPath, function(isExist)
                {
                   if(isExist)
                   {  
                     creatCreative_linkByPath(jpgCopyPath)
                   }
                   else
                   {
                       gif2Jpg(jpgCopyPath, originGifName)
                   }
                })
           }

           f1(jpgCopyPath, originGifName)

        }
        else if(sp[1] =='jpg' || sp[1] =='jpeg' || sp[1] =='png')
        {   
            function f(i)
            {
                creatCreative_linkByPath(localResPath + files[i])
            }
            f(i)
        }
        else
        {
            hasDealFile = hasDealFile + 1
            callbackEnable()
        }
      }
    }

    function callbackEnable()
    {
      if(hasDealFile==filesL)
      {
          onGet( JSON.stringify(creative_link) )
      }
    }


    function gif2Jpg(jpgCopyPath, originGifName)
    {
        var fileWriteStream = fs.createWriteStream( jpgCopyPath )
        console.log(localResPath + originGifName + '.gif')
        readFileInOrder(localResPath + originGifName + '.gif', function(error, data)
        {     
              try
              {
                var gimg = images(data)
              }
              catch(e)
              {
                  console.log(localResPath + originGifName + '.gif' + 'error',e)
                  hasDealFile = hasDealFile + 1
                  callbackEnable()
                  return
              }
              try
              {
                  var saveData = gimg.encode("jpg", {operation:50})
              }
              catch(e)
              {
                  console.log('saveData error',e)
                  hasDealFile = hasDealFile + 1
                  callbackEnable()
                  return
              }
          
              fileWriteStream.write( saveData, function(error)
              { 
                  if(!error)
                  { 
                      creatCreative_linkByPath(jpgCopyPath)
                  }
                  else
                  {
                      hasDealFile = hasDealFile + 1
                      callbackEnable()
                  }
              })
        })
    }

    function creatCreative_linkByPath(filePath)
    {
      readFileInOrder(filePath, function(err, data){
          try
          {
              var img = images(data)
          }
          catch(e)
          {
              console.log(filePath + 'error',e)
              hasDealFile = hasDealFile + 1
              callbackEnable()
              return
          }
          var l = creative_link.length
          creative_link[l] = {}
          
          var urlSuffix = filePath.replace(localResPath,'')
          urlSuffix = urlSuffix.replace(sep,'/')
          creative_link[l].url =  urlPrefix + urlSuffix

          var size = img.size()
          creative_link[l].height = size.height
          creative_link[l].width= size.width  
          hasDealFile = hasDealFile + 1
          callbackEnable()
        })
    }

}

/////////////localize offer end/////////////

/////////////////////////displayUploadCreative/////////////////////////////////

this.displayUploadCreative = function(res) 
{ 
    var body = '<html>'+
    '<head>'+
    '<meta http-equiv="Content-Type" '+
    'content="text/html; charset=UTF-8" />'+
    // '<script src="/javascripts/forClient.js"></script>'+ 
    '<link rel="stylesheet" href="/stylesheets/style.css" />'+
    '</head>'+
    '<body>'+
    '<p>advertiser_id和adv_camp_id用于确定上传资源路径,上传成功返回creativeLink</p>'+
    '<p><a href="/">首页</a></p>'+
    '<form  enctype="multipart/form-data" method="post">'+
    '<table border="0" cellspacing="5">'+
    '<tr>'+
    '  <td>advertiser_id</td>'+
    '  <td><input type="text" name= "advertiser_id" style="width:330px;"/></td>'+
    '</tr>'+
    '<tr>'+
    '  <td>adv_camp_id</td>'+
    '  <td><input type="text" name= "adv_camp_id" style="width:330px;"/></td>'+
    '</tr>'+
    '</table>'+
    '<br><br><input type="file" name="upload" multiple="multiple"><br><br>'+
    '<input type="submit" value="提交" />'+
    '</form>'+
    '</body>'+
    '</html>'


    res.writeHead(200, {"Content-Type": "text/html"})
    res.write(body)
    res.end()
}

this.dealUploadCreativePost = function(req, res)   
{
    var cwd = process.cwd()
    var form = new multiparty.Form(
    {
        encoding:"utf-8",
        uploadDir:cwd,  //文件上传地址
        keepExtensions:true  //保留后缀
    })
    form.parse(req, function(err, fields, files) 
    {
        var localResPath = cwd + sep + 'public' + sep + adResPath + 'creative' + sep + fields.advertiser_id[0] + sep + fields.adv_camp_id[0] + sep
        if(!fs.existsSync(localResPath))
        {
            child_process.execSync(mkdirS + localResPath)  
        }

        for(var i in files.upload)
        {   
            fs.renameSync(files.upload[i].path,localResPath + files.upload[i].originalFilename)
        }
        onGet = function(creative_link)
        {
          res.render('index', { title: 'index' , tips:creative_link})
        }
        getCreativeLinkViaId(fields.advertiser_id[0], fields.adv_camp_id[0], onGet)
    })
   
}

////////////////////////displayUploadCreative end//////////////////////////////////

////////////////////////dealAddToSql//////////////////////////////////

//录入时根据adv_camp_id 因为是异步录入的 浏览器的idx和服务器上的会不一致 
//会出现明明已经提交录入的还会显示在浏览器上 因为还没服务器响应那个时刻没有录入到数据库里 
this.dealAddToSql = function(req, res)   
{
    var queryUrl = url.parse(req.url).query 
    var query = qs.parse(queryUrl)
    var isAll = query.isAll
    var advertiser_id = query.advertiser_id
    if(isAll)
    {
      var offers = this.offers_G[advertiser_id]
      for(var i in offers)
      { 
          var offer = offers[i]
          addOfferToSql(offer, function(){console.log('insertsql')})  
      }

    }
    else
    { 
      var body = req.body
      advertiser_id = body.advertiser_id
      var offers = this.offers_G[advertiser_id]
      for(var i in offers)
      {   
          var offer = offers[i]
          for(var j in req.body)
          {
            if(j=='advertiser_id')
              continue
            if(offer.adv_camp_id == body[j])
            {
              addOfferToSql(offer, function(){console.log('insertsql')}) 
            }
          }
      }
    }

    res.redirect('/offerList/?advertiser_id=' + advertiser_id + '&pageIndex=1')
}


function addOfferToSql(offer, callback) 
{

  writeAllDataToSql(offer, callback)

}


////////////////////////dealAddToSql end//////////////////////////////////


/////////////////////////update sql///////////////////////////////////

var redirect_formatTable = 
{ 
  '000':'&aff_sub={click_id}&aff_sub2={aff_pub}',
  "001":"&dv1={click_id}&nw_sub_aff={aff_pub}",// avaz
  "002":"/{click_id}&placement={aff_pub}", // gli
  "004":"&subid1={click_id}&placement={aff_pub}", // mundu
  "009":"&s1={click_id}&s2={aff_pub}", // leadhug
  "010":"&aff_sub={click_id}&aff_sub8={aff_pub}", // yeahmobi
  "014":"&aff_sub={click_id}&aff_sub2={aff_pub}", // appflood
  "017":"&aff_sub={click_id}&source={aff_pub}", // blackfox
  "021":"&aff_sub={click_id}&aff_sub2={aff_pub}", // zhihe

}

var postback_formatTable = 
{ 
  "000":"add688.com",// 自己
  "003":"http://next.mobvista.com/install?mobvista_clickid={aff_sub}&mobvista_campuuid={aff_offer}&mb_ip={ip}",// mobvi
  "008":"http://net.rayjump.com/conversion?uniq_key=56f3af5d1f07c173&click_id={aff_sub}"
}

var dataDefaultTable = 
{
  //lib_offer
  offer_name:'',   
  group_id:0, 
  advertiser_id:'001',
  adv_camp_id:0,       
  first_pay_in:0,
  pay_type:'cpi',   
  redirect_format:'',
  start_date:'2016-01-01 00:00',    
  end_date:'2017-01-01 00:00',   
  geo_country:'全球用global表示',
  geo_city:'global',          
  geo_country_except:'',
  geo_city_except:'', 
  cap_all_today:'open的话填1000000否则填真实值',
  cap_all:'',  //不填则默认和cap all today 一样  不要默认null因为是text的输入框 默认‘’
  ret_day_need:0,      
  ret_percent_need:0, 
  quality0:'',          
  quality1:'',          
  quality2:'',          
  quality3:'',          
  quality4:'',       
  quality5:'',
  traffic_type:'in-app',      
  traffic_reqirement:'',
  kpi:'',
  platform:'Android',          
  devices:'phones/tables', 
  osversion:'',  
  appid:'',
  operator:'auto',
  payment_terms:30,
  preview_link:'',  
  pack_res_link:'',     
  slogan_description:'',
  remark0:'',
  remark1:'',

  //lib_campaign
  offer_id:'',
  aff_id:'008',
  payin:0, 
  payout:'', //不填则默认和payin 一样
  state:2,
  //start_date:'2016-01-01 00:00',    
  //end_date:'2017-01-01 00:00',  
  //redirect_format:null,
  //geo_country:null,
  //geo_city:'global',
  //geo_country_except:'',
  //geo_city_except:'',
  //cap_all_today:'这里填真实值*100', 
  cap_exceed_count:0,  
  cap_exceed_percent:0,
  force_stop_level:0,  
  force_stop:0,      
  //remark0:null,           
  //remark1:null,

  //interface_info_v1
  pid:'008', 
  campid:'',
  //offer_name:null, 
  status:'running' ,     
  //state:1 ,
  tips:'',
  //platform:'Android',
  price_model:'cpi',   
  price:0,       
  geo:'格式：国家A 国家B ... except 国家C 国家D(global表示全球)',
  tracking_link:'',
  //preview_link:null,
  //start_date:'2016-01-01 00:00',    
    //end_date:'2017-01-01 00:00',  
  performance_criteria:'',
  requirement_traffic:'',
  effective_date: '2016-01-01 00:00',        
  update_date: currentTime() ,  
  app_category:'Tool',
  exclude_device:'',     
  exclude_site:0,  
  carriers:'',
  daily_cap:'open的话填1000000否则填真实值',
  creative_link:'',
  icon_link:'',           
  min_android_version:'', 
  max_android_version:'none', 
  app_name:'',            
  app_desc:'',            
  app_size:0,            
  app_rate:0, 

}

function getAllDataDataDefault()
{
  var allData = {
    offer_name:dataDefaultTable.offer_name,   
    group_id:dataDefaultTable.group_id, 
    advertiser_id:dataDefaultTable.advertiser_id,
    adv_camp_id:dataDefaultTable.adv_camp_id,       
    //first_pay_in:0, 等于paiyin
    pay_type:dataDefaultTable.pay_type,   
    redirect_format:dataDefaultTable.redirect_format, //创建liboffer后 更新（加后缀）
    start_date:dataDefaultTable.start_date,    
    end_date:dataDefaultTable.end_date,   
    geo_country:dataDefaultTable.geo_country,
    geo_city:dataDefaultTable.geo_city,          
    geo_country_except:dataDefaultTable.geo_country_except,
    geo_city_except:dataDefaultTable.geo_city_except, 
    cap_all_today:dataDefaultTable.cap_all_today,
    cap_all:dataDefaultTable.cap_all,  //不填则默认和cap all today 一样
    ret_day_need:dataDefaultTable.ret_day_need,      
    ret_percent_need:dataDefaultTable.ret_percent_need, 
    quality0:dataDefaultTable.quality0,          
    quality1:dataDefaultTable.quality1,          
    quality2:dataDefaultTable.quality2,          
    quality3:dataDefaultTable.quality3,          
    quality4:dataDefaultTable.quality4,          
    quality5:dataDefaultTable.quality5,
    traffic_type:dataDefaultTable.traffic_type,      
    traffic_reqirement:dataDefaultTable.traffic_reqirement,
    kpi:dataDefaultTable.kpi,
    platform:dataDefaultTable.platform,          
    devices:dataDefaultTable.devices, 
    osversion:dataDefaultTable.osversion,  
    appid:dataDefaultTable.appid,
    operator:dataDefaultTable.operator,
    payment_terms:dataDefaultTable.payment_terms,
    preview_link:dataDefaultTable.preview_link,  
    pack_res_link:dataDefaultTable.pack_res_link,     
    slogan_description:dataDefaultTable.slogan_description,
    remark0:dataDefaultTable.remark0,
    remark1:dataDefaultTable.remark1,
  
    //lib_campaign
    //offer_id:null, 创建后生成
    aff_id:dataDefaultTable.aff_id,
    payin:dataDefaultTable.payin, 
    payout:dataDefaultTable.payout,
    state:dataDefaultTable.state,
    cap_exceed_count:dataDefaultTable.cap_exceed_count,  
    cap_exceed_percent:dataDefaultTable.cap_exceed_percent,
    force_stop_level:dataDefaultTable.force_stop_level,  
    force_stop:dataDefaultTable.force_stop,      
  
    //interface_info_v1
    //pid:dataDefaultTable.pid,  等于aff_id
    //campid:null, 创建后生成
    status:dataDefaultTable.status ,     
    tips:dataDefaultTable.tips,
    //price_model:'cpi',  等于pay_type 
    //price:0,    等于payout 
    //geo:'',  等于geo_country
    //tracking_link:'', 创建后生成
    //performance_criteria:'', 等于kpi
    //requirement_traffic:'', 等于traffic_reqirement
    effective_date: dataDefaultTable.effective_date,        
    update_date: dataDefaultTable.update_date ,  
    app_category:dataDefaultTable.app_category,
    exclude_device:dataDefaultTable.exclude_device,     
    exclude_site:dataDefaultTable.exclude_site,  
    carriers:dataDefaultTable.carriers,
    //daily_cap:'这里填真实值*100', 等于cap_all_today
    creative_link:dataDefaultTable.creative_link,
    icon_link:dataDefaultTable.icon_link,           
    min_android_version:dataDefaultTable.min_android_version, 
    max_android_version:dataDefaultTable.max_android_version, 
    app_name:dataDefaultTable.app_name,            
    app_desc:dataDefaultTable.app_desc,            
    app_size:dataDefaultTable.app_size,            
    app_rate:dataDefaultTable.app_rate, 

  }

 return allData
}

writeAllDataToSql = function(data, onWrite)
{ 
  //填充data
  data.first_pay_in = data.payin  
  if (data.cap_all==='')
    data.cap_all = data.cap_all_today 
  data.pid  =  data.aff_id  
  data.price_model = data.pay_type 
  if (data.payout==='')
    data.payout = data.payin 
  data.price = data.payout
  data.app_id =  data.appid
  var a = data.geo_country
  var b = data.geo_country_except
  b = b?' except ' + b:''

  data.geo                  = a + b
  data.performance_criteria = data.kpi
  data.requirement_traffic = data.traffic_reqirement
  data.daily_cap  =  data.cap_all_today  

  function onWriteLibOfferToSql(result)
  {
    if(result.insertSuccess)
    {
      data.offer_id = result.outData.offer_id 
      data.redirect_format = data.redirect_format + (redirect_formatTable[data.advertiser_id] || redirect_formatTable['000'])

      writeLibCampaignToSql(data, onWriteLibCampaignToSql)
    }
    else
    {
      onWrite(0)
      console.log('writeLibOfferToSql error')
    }
  }

  function onWriteLibCampaignToSql(result)
  {
    if(result.insertSuccess)
    {
      data.campaign_id = result.outData.campaign_id 
      data.tracking_link = result.outData.tracking_link 
      data.postback_format = result.outData.postback_format 
      data.campid = result.outData.campaign_id  

      writeInterfaceInfoToSql(data, onWriteInterfaceInfoToSql)
    }
    else
    {   
        onWrite(1)
        console.log('writeLibCampaignToSql error')
    }
  }

  function onWriteInterfaceInfoToSql(result)
  {
    onWrite(result.insertSuccess?200:2)
  }

  writeLibOfferToSql(data, onWriteLibOfferToSql)

}

//将表单的数据录入sql 并设置offer_id
writeLibOfferToSql = function(data, onWrite)
{
  //过滤非录入数据 
  data.id = null //null自增
  data.offer_id = '' //录入完成后计算
  data.cap_run_today = 0 //运行时变量
  data.cap_run = 0 //运行时变量
  //填充data
  if(data.cap_all==='') 
    data.cap_all = data.cap_all_today

  var connection = getSqlConnection()
  connection.connect();  
  //执行SQL语句
  var element =['id', 'offer_id', 'offer_name', 'group_id', 'advertiser_id', 'adv_camp_id', 'first_pay_in', 'pay_type', 'redirect_format', 'start_date', 'end_date', 'geo_country', 'geo_city', 'geo_country_except', 'geo_city_except', 'cap_all_today', 'cap_run_today', 'cap_all', 'cap_run', 'ret_day_need', 'ret_percent_need', 'quality0', 'quality1', 'quality2', 'quality3', 'quality4', 'quality5', 'traffic_type', 'traffic_reqirement', 'kpi', 'platform', 'devices', 'osversion', 'appid', 'operator', 'payment_terms', 'preview_link', 'pack_res_link', 'slogan_description', 'remark0', 'remark1', 'creative_link', 'icon_link']
  var sql1 = '('
  for (var i=0;i<element.length;i++)
  {
    sql1 = sql1 + element[i] 
    if (i == element.length -1)
      sql1 = sql1 + ')'
    else
      sql1 = sql1 + ','
  }
  console.log('INSERT lib_offer sql1:',sql1);     
  var  sql = 'INSERT INTO lib_offer' + sql1 + 'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
  var  params = [];
  for (var i=0;i<element.length;i++)
  {
    if(element[i]== 'geo_country' && data[element[i]] =='global')
      params[i] = 'AO AF AL DZ AD AI AG AR AM AU AT AZ BS BH BD BB BY BE BZ BJ BO BW BR BN BG BF BM BT MM BI CM CA CF TD CL CN CO CG KH CR CU CY CZ CK DK DJ DO EC EG SV EE ET FJ FI FR GF GA GM GE DE GH GI GR GD GU GT GN GY HT HN HK HU IS IN ID IR IQ IE IL IT KT JM JP JO KZ KE KR KW KG KH LA LV LB LS LR LY LI LT LU MO MG MW MS MY MV ML MT MU MR MX MD MC MN MA MZ MM NA NZ NR NP NL NI NE NG KP NO OM PK PA PG PY PE PH PL PF PT PR QA RO RU LC VC SM ST SA SN SC SL SG SK SI SB SO ZA ES LK LC VC SD SR SZ SE CH SY TW TJ TZ TH TG TO TT TL TN TR TM UG UA AE GB US UY UZ TZ VE VN YE YU ZA ZW ZR ZM'
    else
      params[i] = data[element[i]]
  }
  console.log(sql, params)
  connection.query(sql,params, 
    function(err, rows) 
    { 
        if (err) 
        {
            console.log('INSERT lib_offer error:',err.message);
            connection.end();
            var result =  {insertSuccess: false, message: 'insert lib_offer fail'}
            onWrite(result)
            return
        }
        console.log('INSERT lib_offer success:',rows);    

        var insertId = rows.insertId

        var sql = 'UPDATE lib_offer SET offer_id = ? WHERE id = ?';
        var params = [insertId + 100000, insertId];

        connection.query(sql,params,function (err, rows) 
        {
          if(err){
            console.log('UPDATE lib_offer error:',err.message);
            connection.end();
            var result =  {insertSuccess: true, outData:{}, message: 'insert lib_offer success,update offer_id fail'}
                onWrite(result) 
                return
          }        
          console.log('UPDATE lib_offer success',rows);

          connection.end();
          var offerId = insertId + 100000
          var out = {offer_id:offerId,}
          var result = { insertSuccess: true, outData:out, message: 'insert lib_offer success,offer_id:' + offerId}
          onWrite(result) 
        })
     }
  ); 

}

writeLibCampaignToSql = function(data, onWrite)
{ 

  //过滤非录入数据
  data.id = null
  data.campaign_id = '' //录入完成后计算
  data.cap_run_today = 0
  data.cap_run = 0
  data.tracking_link = '' //录入完成后计算
  data.postback_format = '' //录入完成后计算

  //填充
  if (data.payout==='')
    data.payout = data.payin

  var connection = getSqlConnection()
  connection.connect();  
  var element =[ 'id', 'campaign_id', 'offer_id', 'aff_id', 'payin', 'payout', 'state', 'start_date', 'end_date', 'redirect_format', 'postback_format', 'geo_country', 'geo_city', 'geo_country_except', 'geo_city_except', 'tracking_link', 'cap_all_today', 'cap_run_today', 'cap_run', 'cap_exceed_count', 'cap_exceed_percent', 'force_stop_level', 'force_stop', 'remark0', 'remark1']
  var sql1 = '('
  for (var i=0;i<element.length;i++)
  {
    sql1 = sql1 + element[i] 
    if (i == element.length -1)
      sql1 = sql1 + ')'
    else
      sql1 = sql1 + ','
  }
  console.log('INSERT sql1:',sql1);     
  var  sql = 'INSERT INTO lib_campaign' + sql1 + 'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
  var  params = [];
  for (var i=0;i<element.length;i++)
  { 
    if(element[i]== 'geo_country' && data[element[i]] =='global')
      params[i] = 'AO AF AL DZ AD AI AG AR AM AU AT AZ BS BH BD BB BY BE BZ BJ BO BW BR BN BG BF BM BT MM BI CM CA CF TD CL CN CO CG KH CR CU CY CZ CK DK DJ DO EC EG SV EE ET FJ FI FR GF GA GM GE DE GH GI GR GD GU GT GN GY HT HN HK HU IS IN ID IR IQ IE IL IT KT JM JP JO KZ KE KR KW KG KH LA LV LB LS LR LY LI LT LU MO MG MW MS MY MV ML MT MU MR MX MD MC MN MA MZ MM NA NZ NR NP NL NI NE NG KP NO OM PK PA PG PY PE PH PL PF PT PR QA RO RU LC VC SM ST SA SN SC SL SG SK SI SB SO ZA ES LK LC VC SD SR SZ SE CH SY TW TJ TZ TH TG TO TT TL TN TR TM UG UA AE GB US UY UZ TZ VE VN YE YU ZA ZW ZR ZM'
    else
      params[i] = data[element[i]]

    console.log('INSERT lib_campaign params:',params[i]);     
  }

  connection.query(sql,params, 
    function(err, rows) 
    { 
        if (err) 
        {
            console.log('INSERT lib_campaign error:',err.message);
            connection.end();
            var result =  {insertSuccess: false,message: 'insert lib_campaign fail'}
            onWrite(result)
            return
        }
        console.log('INSERT lib_campaign success:',rows);    

        var insertId = rows.insertId

        var sql = 'UPDATE lib_campaign SET campaign_id = ?,tracking_link = ?,postback_format = ? WHERE id = ?';
        var campaign_id = insertId + 100000
        if(data.aff_id=='003')
          var tracking_link = 'http://ad688.com/offer/' + data.aff_id + '/' + campaign_id + '/?aff_offer={campaign}&aff_pub={publisher}&aff_sub={click_id}'
        else if(data.aff_id=='008')
          var tracking_link = 'http://ad688.com/offer/' + data.aff_id + '/' + campaign_id + '/?aff_pub={subId}&aff_sub={click_id}'
        var postback_format = postback_formatTable[data.aff_id] || postback_formatTable['000']
        var params = [campaign_id, tracking_link, postback_format, insertId];

        connection.query(sql,params,function (err, rows) 
        {
          if(err){
            console.log('UPDATE error:',err.message);
            connection.end();
            var result =  {insertSuccess: true, outData:{}, message: 'insert lib_campaign success,update campaign_id fail'}
                onWrite(result) 
                return
          }        
          console.log('UPDATE success',rows);

          connection.end();
          var campaign_id = insertId + 100000
          var out = {campaign_id:campaign_id, tracking_link:tracking_link,postback_format:postback_format, }
          var result = { insertSuccess: true, outData:out, message: 'insert lib_campaign success,campaign_id:' + campaign_id}
          onWrite(result) 
        })
     }
  ); 
}

writeInterfaceInfoToSql = function(data, onWrite)
{ 
  //过滤非录入数据
  data.id = null

  //填充data
  // var c = 'http://game520.com/offer/creatives/' + data.campid + '/' + data.campid + '.zip' 
  // if (!data.creative_link)
  //   data.creative_link = c
  

  var connection = getSqlConnection()
  connection.connect();  
  //执行SQL语句
  var element =['id','pid','campid','offer_name','status','state','tips','platform','price_model','price','geo','tracking_link','preview_link','start_date','end_date','performance_criteria','requirement_traffic','effective_date','update_date','app_category','exclude_device','exclude_site','carriers','daily_cap','creative_link','icon_link','min_android_version','max_android_version','app_name','app_desc','app_size','app_rate','app_id']
  var sql1 = '('
  for (var i=0;i<element.length;i++)
  {
    sql1 = sql1 + element[i] 
    if (i == element.length -1)
      sql1 = sql1 + ')'
    else
      sql1 = sql1 + ','
  }
  console.log('INSERT sql1:',sql1);     
  var  sql = 'INSERT INTO interface_info_v1' + sql1 + 'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
  var  params = [];
  for (var i=0;i<element.length;i++)
  { 

    params[i] = data[element[i]] 
    console.log('INSERT interface_info_v1 params:',params[i]);     
  }

  connection.query(sql,params, 
    function(err, rows) 
    { 
        if (err) 
        {
            console.log('INSERT interface_info_v1 error:',err.message);
            connection.end();
            var result =  {insertSuccess: false,message:'insert interface_info_v1 fail'}
            onWrite(result)
            return
        }
        console.log('INSERT interface_info_v1 success:',rows,data.offer_name);    
        connection.end()

      // var fs = require("fs")

      // fs.readFile('./public/emailBody.json', function (err, fileData) { 
      //   var emailTitle = '<br><br>New offer ['+data.offer_name+'] ad688-'+data.campid+'- AD688.COM<br><br>'
      //   var emailbody = fileData.toString()

      //   var reg = new RegExp("\r\n","g"); 
      //   data.geo= data.geo.replace(reg,"<br>")
      //   data.requirement_traffic= data.requirement_traffic.replace(reg,"<br>") 
      //   data.performance_criteria= data.performance_criteria.replace(reg,"<br>")

      //   emailbody = emailbody.replace('XXXcampidXXX', data.campid)
      //   emailbody = emailbody.replace('XXXoffer_nameXXX', data.offer_name)
      //   emailbody = emailbody.replace('XXXgeoXXX', data.geo)
      //   emailbody = emailbody.replace('XXXcamType1XXX', camType1Table[data.platform])
      //   emailbody = emailbody.replace('XXXcamType2XXX', 'non incent') 
      //   emailbody = emailbody.replace('XXXplatform1XXX', data.platform) 
      //   emailbody = emailbody.replace('XXXplatform2XXX', data.platform) 
      //   var cap = data.daily_cap
      //   if (cap >= 1000000)
      //     cap = "OPEN"
      //   emailbody = emailbody.replace('XXXdaily_capXXX', cap)
      //   emailbody = emailbody.replace('XXXpriceXXX', data.price)
      //   emailbody = emailbody.replace('XXXrequirement_trafficXXX', data.requirement_traffic)
      //   emailbody = emailbody.replace('XXXperformance_criteriaXXX', data.performance_criteria)
      //   emailbody = emailbody.replace('XXXtracking_linkXXX', data.tracking_link)
      //   emailbody = emailbody.replace('XXXpreview_linkXXX', data.preview_link)

            var result = { insertSuccess: true, message:'insert interface_info_v1 success' }
            onWrite(result) 
      // });

    }) 
}

/////////////////////////update sql end///////////////////////////////////




/////////////////////////updateCreativeLink2///////////////////////////////////


//更新interface_info的CreativeLink2 过滤1200x627..
this.displayUpdateCreativeLink2 = function(res)
{

    var body = '<html>'+
    '<head>'+
    '<meta http-equiv="Content-Type" '+
    'content="text/html; charset=UTF-8" />'+
    // '<script src="/javascripts/forClient.js"></script>'+ 
    '<link rel="stylesheet" href="/stylesheets/style.css" />'+
    '</head>'+
    '<body>'+
    '<p>正则说明：</p>'+
    '<p>!!表示对正则表达式取反 </p>'+
    '<p>&&表示并且 </p>'+
    '<p>||表示或者</p>'+
    '<p>((  xxxxx  ))</p>'+
    '<p>!! && ||属于同级</p>'+
    '<p>isNeedLoadRegexp含义:如果是 (非gif格式并且含有123x123) 或者 (不是图片格式) 则不要下载  取反需要下载</p>'+
    '<p><a href="/">首页</a></p>'+
    '<form  method="post">'+
    '<table border="0" cellspacing="5">'+
    '<tr>'+
    '  <td>advertiser_id</td>'+
    '  <td><input type="text" name= "advertiser_id" style="width:330px;"/></td>'+
    '</tr>'+
    '<tr>'+
    '  <td>adv_camp_id</td>'+
    '  <td><input type="text" name= "adv_camp_id" style="width:330px;"/></td>'+
    '</tr>'+
    '<tr>'+
    '  <td>isNeedLoadRegexp</td>'+//如果是 (非gif格式并且含有123x123) 或者 (不是图片格式) 则不要下载  取反需要下载
    '  <td><input type="text" name= "isNeedLoadRegexp" value="!!(((([0123456789]+x[0123456789]&&!!\\.gif))||!!((\\.gif|\\.png|\\.jpg|\\.jpeg))))" style="width:330px;"/></td>'+
    '</tr>'+
    '<tr>'+
    '  <td>isNeedInsertRegexp</td>'+//非gif格式并且含有123x123 需要插入creativeLink
    '  <td><input type="text" name= "isNeedInsertRegexp" value="[0123456789]+x[0123456789]&&!!\\.gif" style="width:330px;"/></td>'+
    '</tr>'+
    '</table>'+
    '<input type="submit" value="提交" />'+
    '</form>'+
    '</body>'+
    '</html>'


    res.writeHead(200, {"Content-Type": "text/html"})
    res.write(body)
    res.end()

}

///  [0123456789]+x[0123456789]+|res[0123456789]+x[0123456789]+
this.dealUpdateCreativeLink2 = function(req, res)   
{
    //拉取api 获得一个offers
 
    //取出所有advertiser_id符合的offer 根据这些offer统一下载资源下载好后 逐个获取creativeLink，iconLink 
    //然后根据offerID找到interfaceinfo
    var body = req.body
    var advertiser_id = body.advertiser_id
    var adv_camp_id = body.adv_camp_id

    var isNeedLoadRegexp = body.isNeedLoadRegexp
    var isNeedInsertRegexp = body.isNeedInsertRegexp

    
    var offers
    var subOffers = []
    var env = this
    var connection = getSqlConnection()

    onGetApi = function(postData)
    {
        offers = parseApiToObj(postData, advertiser_id) 
        for(var i in offers)
        {
            formatOffer(advertiser_id, offers[i]) 
        } 
        //console.log('offer123:',offers)  

        loadResesByAdvertiser_id(advertiser_id)
    }

    getApi(advertiser_id, onGetApi)

    function getOfferByAdvId(adv_camp_id)
    {
        for(var i in offers)
        {
          if( parseInt(offers[i].adv_camp_id) == parseInt(adv_camp_id) )
          {
            return offers[i]
          }
        }
    }

    function loadResesByAdvertiser_id(advertiser_id)
    {
        var sql = 'SELECT * FROM lib_offer WHERE advertiser_id = ' + advertiser_id
        if(adv_camp_id) 
          sql = sql + ' and adv_camp_id = ' + adv_camp_id
        connection.query(sql, function (err, rows) 
        {   
            if(err)
            {
              console.log('SELECT * FROM lib_offer error - ',err.message);
              return   
            }    
            for(var i in rows)      
            {
                if(rows[i].adv_camp_id && getOfferByAdvId(rows[i].adv_camp_id))    
                { 
                  var l = subOffers.length
                  subOffers[l] = getOfferByAdvId(rows[i].adv_camp_id)
                  subOffers[l].offer_id = rows[i].offer_id
                }  
            }   
            env.loadCreatives(advertiser_id, subOffers, null, onLoadAllCreative, isNeedLoadRegexp, isNeedInsertRegexp)
    
        })  
    }


    function onLoadAllCreative()
    {
      function fun(i)
      {   
          var offer = subOffers[i]
          function onIcon_linkUpdate(preview_link, icon_link)
          {
              updateInterfaceOfSql(offer.offer_id, icon_link)
          }
              
          function onCreative_LinkUpdate(creative_link)
          {
            updateInterfaceOfSql(offer.offer_id, null, creative_link)
          }
          if(offer.icon_link)
            onIcon_linkUpdate(null,offer.icon_link)
          else
            env.icon_linkUpdate(advertiser_id, offer.preview_link, offer.redirect_format, offer.platform, onIcon_linkUpdate) 
          env.creative_LinkUpdate(advertiser_id, offer.adv_camp_id, onCreative_LinkUpdate)
      }
      for(var i in subOffers)
      {
        fun(i)
      }
    }

    function updateInterfaceOfSql(offer_id, icon_link, creative_link)
    {
        var sql = 'SELECT * FROM lib_campaign WHERE offer_id = ' + offer_id
        connection.query(sql, function (err, rows) 
        {
            if(err)
            {
              console.log('SELECT * FROM lib_campaign error - ',err.message);
              return   
            }  
            for(var i in rows)
            {
                var sql1 = 'SELECT * FROM interface_info_v1 WHERE campid = ' + rows[i].campaign_id
                connection.query(sql1, function (err, rows1) 
                {
                    if(err)
                    {
                      console.log('SELECT * FROM interface_info_v1 error - ',err.message);
                      return   
                    }  
                    for(var j in rows1)
                    {
                        if(icon_link)
                          var  sql = "UPDATE interface_info_v1 SET icon_link2 = '" + icon_link +"' WHERE interface_info_v1.id =" + rows1[j].id
                        else if(creative_link)
                          var  sql = "UPDATE interface_info_v1 SET creative_link2 ='" + creative_link +"' WHERE interface_info_v1.id =" + rows1[j].id
                        connection.query(sql, 
                        function(err, rows2) 
                        { 
                            if (err) 
                            {
                                console.log('UPDATE interface_info_v1 error:',err.message);
                                return
                            }
                            console.log('UPDATE interface_info_v1 success:',rows2)
                        })
                    }
                })
            }
        })
    }

    res.render('index', { title: 'index' , tips: "choose a operation:"})

}


/////////////////////////updateCreativeLink2 end///////////////////////////////////

